	
	package Dao.daoimpl;
	import java.sql.Connection;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Statement;
	import java.util.ArrayList;
	import java.util.List;
	import unitls.DataBaseConnection;
	import enitys.Grade;
	import Dao.GradeDao;
	
	public class GradeDaoImpl implements GradeDao{
		private Connection conn = null;
		private Statement stmt = null;
		private ResultSet rs = null;
		private String sql = null;// sql语句
		
		public List<Grade> gradeAllinfo() throws Exception {
			// TODO Auto-generated method stub
			List<Grade> list =new ArrayList<Grade>();
			//sql ="select g.g_id,u.s_id,u.username,c.c_id,c.c_name,c.teacher,c.score,g.grade from grade g,user u,course c"
					//+ "where g.s_id = u.s_id and g.c_id = c.c_id";
			sql="select g.g_id,g.username,course.c_name,course.teacher,course.score,g.grade from(select grade.g_id,username,c_id,grade from grade join user on (grade.s_id=user.s_id)) g join course on (course.c_id=g.c_id)";
			conn=DataBaseConnection.getConnection();
			try{
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);                              
			while(rs.next()){
				Grade grade=new Grade();
				grade.setG_id(rs.getInt("g_id"));
				grade.setC_name(rs.getString("c_name"));
				grade.setTeacher(rs.getString("teacher"));
				grade.setScore(rs.getString("score"));
				grade.setUsername(rs.getString("username"));
				grade.setGrade(rs.getString("grade"));
				list.add(grade);
			}
			}catch(SQLException sqle) {
				sqle.printStackTrace();
			} finally {
				// 关闭连接
				DataBaseConnection.closeRs(this.rs);
				DataBaseConnection.closeStmt(this.stmt);
				DataBaseConnection.closeConn(this.conn);
			}
			
			return list;
		}

		public List<Grade> personGrade(String  username)
				throws Exception {
			// TODO Auto-generated method stub
			List<Grade> list =new ArrayList<Grade>();
			sql="select grade.g_id ,username ,c_name,teacher,score,grade  from (select g.g_id g_id,g.username username,course.c_name c_name,course.teacher teacher,course.score score,g.grade grade from"+
					"(select grade.g_id,username,c_id,grade from grade join user on"+
								"(grade.s_id=user.s_id)) g join course on (course.c_id=g.c_id)) a where username='"+username+"'";
			conn=DataBaseConnection.getConnection();
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			while(rs.next()){
				Grade grade=new Grade();
				grade.setC_name(rs.getString("c_name"));
				grade.setTeacher(rs.getString("teacher"));
				grade.setScore(rs.getString("score"));
				grade.setGrade(rs.getString("grade"));
				grade.setUsername(rs.getString("username"));
				grade.setG_id(rs.getInt("g_id"));
				list.add(grade);
			}
			return list;
		}

		public boolean UpdateGrade(Grade grade) throws Exception {
			// TODO Auto-generated method stub
			int rs=0;
			sql="update grade set grade="+grade.getGrade()+" where g_id="+grade.getG_id();
			this.conn = DataBaseConnection.getConnection();
			try {
				this.stmt = this.conn.createStatement();
				rs = this.stmt.executeUpdate(this.sql);
				if (rs > 0) {
					return true;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				// 关闭连接
				DataBaseConnection.closeStmt(this.stmt);
				DataBaseConnection.closeConn(this.conn);
			}
			
			return false;
		}

		public int deleteGrade(int id) throws Exception {
			// TODO Auto-generated method stub
			// 存储删除的条数 作返回参数使用
			int rs = 0;
			// 设置sql语句
			this.sql = "delete from grade where g_id = '" + id + "'";
			this.conn = DataBaseConnection.getConnection();
			try {
				this.stmt = this.conn.createStatement();
				rs = this.stmt.executeUpdate(this.sql);
			} catch (SQLException sqle) {
				sqle.printStackTrace();
			} finally {
				// 关闭连接
				DataBaseConnection.closeStmt(this.stmt);
				DataBaseConnection.closeConn(this.conn);
			}
			return rs;
		}

		public boolean addGrade(Grade grade) throws Exception {
			// 查询出学生id
			this.sql = "select * from user where username = '" + grade.getUsername() + "'";
			this.conn = DataBaseConnection.getConnection();
			try {
					this.stmt = this.conn.createStatement();
					this.rs = this.stmt.executeQuery(this.sql);
					if (rs.next()) {
						int stu_id = rs.getInt("s_id");
						// 查询是否已经添加过该课程
						this.sql = "select * from grade where s_id = '" + stu_id + "' and c_id = '" + grade.getC_id() + "'";
						this.rs = this.stmt.executeQuery(this.sql);
					if (!rs.next()) {
						this.sql = "insert into grade(s_id,c_id,grade) values('" + stu_id + "','" + grade.getC_id()
									+ "','" + grade.getGrade() + "')";
							this.stmt.executeUpdate(this.sql);
							return true;
						}
					}
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
				// 关闭连接
					DataBaseConnection.closeRs(this.rs);
					DataBaseConnection.closeStmt(this.stmt);
					DataBaseConnection.closeConn(this.conn);
				}
			return false;
		}
	
	}
